package DBLayer;

import java.util.ArrayList;
import ModelLayer.Customer;
import java.sql.*;

public class DbCustomer implements IFDBCustomer {
	private Connection con;
	private DbLocation locationDB;
	//Public methods
	
	public DbCustomer()
	{
		con = DbConnection.getInstance().getDBcon();
		locationDB = new DbLocation();
	}
	
	@Override
	public ArrayList<Customer> getAllCustomers() {
		return miscWhere("");
	}

	@Override
	public Customer getCustomerByPhoneNo(String _phoneNo) {
		String wClause = "phone = '" + _phoneNo + "'";
		System.out.println("Search Customer " + wClause);
		return singleWhere(wClause);
	}

	@Override
	public Customer getCustomerByName(String _name) {
		String wClause = "fName = '" + _name + "'";
		System.out.println("Search Customer " + wClause);
		return singleWhere(wClause);
	}
	
	@Override
	public Customer getCustomerById(int _id) {
		String wClause = "p.id = '" + _id + "'";
		System.out.println("Search Customer " + wClause);
		return singleWhere(wClause);
	}

	@Override
	public int insertCustomer(Customer _cust)  {
		int nextId = GetMax.getMaxId("SELECT max(id) from people");
		nextId++;
		int rc = -1;
		String queryP="INSERT INTO People(id, name, address, phone, locationId)  VALUES('"+
				nextId + "','" +
				_cust.getName() + "','"  +
				_cust.getAddress()  + "','"  +
				_cust.getPhoneNO()  + "','"  +
				_cust.getLocation().getId()  + "')";
		
		String queryC="INSERT INTO Customer(lName, cType)  VALUES('"+
				nextId + "','"  +
				_cust.getSurname() + "','"  +
				_cust.getType()  + "')";
		
		System.out.println("insert : " + queryP + "\n" + queryC);
		try{ // insert new customer
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(queryP);
			rc = stmt.executeUpdate(queryC);
			stmt.close();
		}//end try
		catch(SQLException ex){
			System.out.println("Customer was inserted");
		}
		return(rc);
	}

	@Override
	public int updateCustomer(Customer customer) {
		Customer custObj = customer;
		int rc=-1;

		String queryP="UPDATE People SET "+
				"fName ='"+ custObj.getName()+"', "+
				"pAddress ='"+ custObj.getAddress() + "', " +
				"phone ='"+ custObj.getPhoneNO() + "', " +
				" WHERE id = '"+ custObj.getId() + "'";
		
		String queryC="UPDATE customer SET "+
				"lName ='"+ custObj.getSurname()+"', "+
				"cType ='"+ custObj.getType() +
				" WHERE id = '"+ custObj.getId() + "'";
		
		System.out.println("Update query:" + queryP + "\n" + queryC);
		try{ // update employee
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(queryP);
			rc = stmt.executeUpdate(queryC);

			stmt.close();
		}//end try
		catch(Exception ex){
			System.out.println("Update exception in customer db: "+ex);
		}
		return(rc);
	}

	@Override
	public int deleteCustomer(Customer _cust) {
		int rc=-1;

		String query="DELETE FROM People WHERE phone = '" +
				_cust.getPhoneNO() + "'";
		System.out.println(query);
		try{ // delete from customer
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();  
		}//end try	
		catch(Exception ex){
			System.out.println("Delete exception in customer db: "+ex);
		}
		return(rc);
	}

	//private methods

	//michWere is used whenever we want to select more than one customer
	private ArrayList<Customer> miscWhere(String wClause)
	{
		ResultSet results;
		ArrayList<Customer> list = new ArrayList<Customer>();	
		String query =  buildQuery(wClause);

		try{ // read the PrivateCustomer from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while( results.next() )
			{
				Customer custObj = new Customer();
				custObj = buildCustomer(results);				
				list.add(custObj);		
			}//end while
			stmt.close();     

		}//end try	
		catch(Exception e){
			System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}

		return list;
	}

	//Singlewhere to select only one Customer 	
	private Customer singleWhere(String wClause)
	{
		ResultSet results;
		Customer custObj = new Customer();
		
	
			String query =  buildQuery(wClause);
			System.out.println(query);
			try{ // read the customer from the database
				Statement stmt = con.createStatement();
				stmt.setQueryTimeout(5);
				results = stmt.executeQuery(query);

				if( results.next() ){
					custObj = buildCustomer(results);
					stmt.close();
				}		
				else{ //no customer was found
					custObj = null;
				}
			}
			catch(Exception e){
				System.out.println("Query exception: "+e);
			}

		return custObj;
	}

	//method to build the query for Customer
	private String buildQuery(String wClause)
	{
		String query="SELECT p.id, fName, pAddress, phone, locationId, cType, lName FROM People p, Customer c";

		if (wClause.length()>0)
			query=query+" WHERE " + wClause + " AND c.id=p.id";
		else
			query=query+" WHERE c.id=p.id";

		return query;
	}

	//method to build a Customer object
	private Customer buildCustomer(ResultSet results)
	{   
		Customer custObj = new Customer();
		try{ // the columns from the table customer  are used
			custObj.setId(Integer.valueOf(results.getString("id")));
			custObj.setName(results.getString("fName"));
			custObj.setAddress(results.getString("pAddress"));
			custObj.setPhoneNO(results.getString("phone"));
			custObj.setSurname(results.getString("lName"));
			custObj.setType(results.getString("cType"));
			custObj.setLocation(locationDB.getLocationById(results.getInt(("locationId"))));
		}
		catch(Exception e)
		{
			System.out.println("error in building the customer object");
		}
		return custObj;
	}
}

